#include <db_manager/dao/user_dao.h>

#include <iostream>

#include <db_manager/qls_db_conn_pool.h>

/* static */
QCache<QString, User> UserDao::userCache;
QCache<QString, QList<User>> UserDao::usersCache;

UserDao::UserDao()
{
}

/* 根据id查找 */
User *UserDao::findOne(const quint32 &id)
{
    /* 缓存 */
    QString key = QString("findOne%1").arg(id);
    if (userCache.contains(key))
        return userCache.object(key);

    /* 组合sql语句 */
    QString content = QString("select * from `user` where id = %1").arg(id);

    //获得查询结果
    QSqlRecord *rec = QLsDBConnPool::runOne(content);

    //判空
    if (rec == nullptr)
        return nullptr;

    //将查询的结果存入User类的对象中
    User *user = new User();
    user->setId(rec->value("id").toUInt());
    user->setUsername(rec->value("username").toString());
    user->setPassword(rec->value("password").toString());
    user->setMobile(rec->value("mobile").toString());
    user->setEmail(rec->value("email").toString());
    user->setDate(rec->value("date").toString());

    /* 加入缓存 */
    userCache.insert(key, user);

    return user;
}

/* 根据自定义条件查找 */
User *UserDao::findOne(const QLsSpecification &spec)
{
    /* 缓存 */
    if (userCache.contains("findOne" + spec.getSqlWhere()))
        return userCache.object("findOne" + spec.getSqlWhere());

    //构建sql语句
    QString content =
        QString("select * from `user` where %1").arg(spec.getSqlWhere());

    //获得查询结果
    QSqlRecord *rec = QLsDBConnPool::runOne(content);

    //判空
    if (rec == nullptr)
        return nullptr;

    //将查询的结果存入User类的对象中
    User *user = new User;
    user->setId(rec->value("id").toUInt());
    user->setUsername(rec->value("username").toString());
    user->setPassword(rec->value("password").toString());
    user->setMobile(rec->value("mobile").toString());
    user->setEmail(rec->value("email").toString());
    user->setDate(rec->value("date").toString());

    /* 加入缓存 */
    userCache.insert("findOne" + spec.getSqlWhere(), user);

    return user;
}

QList<User> UserDao::findAll(const QLsSpecification &spec)
{
    if (usersCache.contains("findAll" + spec.getSqlWhere()))
        return *usersCache.object("findAll" + spec.getSqlWhere());

    QList<User> res;

    //构建sql语句
    QString content =
        QString("select * from `user` %1")
            .arg((spec.getSqlWhere() == "" ? ""
                                           : "where " + spec.getSqlWhere()));

    //获得查询结果
    QList<QSqlRecord> rec = QLsDBConnPool::runAll(content);

    //将查询的结果存入QList<User>链表中
    for (auto it = rec.begin(); it != rec.end(); it++) {
        User user;
        user.setId(it->value("id").toUInt());
        user.setUsername(it->value("username").toString());
        user.setPassword(it->value("password").toString());
        user.setMobile(it->value("mobile").toString());
        user.setEmail(it->value("email").toString());
        user.setDate(it->value("date").toString());
        res.push_back(user);
    }

    /* 加入缓存 */
    usersCache.insert("findAll" + spec.getSqlWhere(), &res);

    return res;
}

/* 保存 */
void UserDao::save(const User *user)
{
    //先判断该用户数据是否存在，以主键id来判断
    QString content =
        QString("select * from `user` where id = %1").arg(user->getId());
    QSqlRecord *rec = QLsDBConnPool::runOne(content);

    content.clear();
    if (rec == nullptr) { //如果不存在，则insert
        content = QString("insert into `user` values(null, %1, %2, %3, %4, %5)")
                      .arg(QLsSpecification::tranString(user->getUsername()))
                      .arg(QLsSpecification::tranString(user->getPassword()))
                      .arg(QLsSpecification::tranString(user->getMobile()))
                      .arg(QLsSpecification::tranString(user->getEmail()))
                      .arg(QLsSpecification::tranString(user->getDate()));
        QLsDBConnPool::runNo(content);
    } else { //如果存在，则update
        content = QString("update `user` set username = %1, password = %2, "
                          "mobile = %3, email = %4,"
                          "date = %5 where id = %6")
                      .arg(QLsSpecification::tranString(user->getUsername()))
                      .arg(QLsSpecification::tranString(user->getPassword()))
                      .arg(QLsSpecification::tranString(user->getMobile()))
                      .arg(QLsSpecification::tranString(user->getEmail()))
                      .arg(QLsSpecification::tranString(user->getDate()))
                      .arg(user->getId());
        QLsDBConnPool::runNo(content);
    }

    /* 清理缓存 */
    userCache.clear();
    usersCache.clear();
}

/* 删除 */
void UserDao::del(const quint32 &id)
{
    //先判断该用户数据是否存在，以主键id来判断
    QString content = QString("select * from `user` where id = %1").arg(id);
    QSqlRecord *rec = QLsDBConnPool::runOne(content);

    content.clear();
    if (rec == nullptr) { //判空
        qDebug() << "无此用户";
        return;
    }

    content = QString("delete from `user` where id = %1").arg(id);
    QLsDBConnPool::runNo(content);

    /* 清理缓存 */
    userCache.clear();
    usersCache.clear();
}

/* 查询表是否存在 */
bool UserDao::exists(const QString &databaseType)
{
    if (databaseType == "MySQL") {
        QSqlRecord *rec = QLsDBConnPool::runOne(
            "select * "
            "from information_schema.tables "
            "where table_schema='qlpmsp' and table_name = 'user';");
        return rec;
    } else if (databaseType == "SQLite") {
        QSqlRecord *rec =
            QLsDBConnPool::runOne("select * "
                                  "from sqlite_master "
                                  "where type = 'table' and name = 'user';");
        return rec;
    }

    /* 清理缓存 */
    userCache.clear();
    usersCache.clear();

    return false;
}

/* 创建表 */
void UserDao::CreateTable(const QString &databaseType)
{
    /* mysql */
    if (databaseType == "MySQL") {
        /* 判断数据库是否存在，不存在则创建数据库，切换到该数据库 */
        QLsDBConnPool::runNo("create 'qlpmsp' if not exists `qlpmsp`;");
        QLsDBConnPool::runNo("use qlpmsp;");

        /* 如果表存在则删除之前的，创建新表 */
        QLsDBConnPool::runNo("DROP TABLE IF EXISTS `user`;");
        QLsDBConnPool::runNo(
            "CREATE TABLE `user` ("
            "  `id` int(11) NOT NULL AUTO_INCREMENT,"
            "  `username` varchar(16) DEFAULT NULL COMMENT '用户名',"
            "  `password` varchar(16) DEFAULT NULL COMMENT '密码',"
            "  `mobile` varchar(11) DEFAULT NULL COMMENT '手机号',"
            "  `email` varchar(48) DEFAULT NULL COMMENT '邮箱',"
            "  `date` varchar(19) DEFAULT NULL COMMENT '注册时间',"
            "  PRIMARY KEY (`id`)"
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
    }
    /* sqlite */
    else if (databaseType == "SQLite") {
        /* 如果表存在则删除之前的，创建新表 */
        QLsDBConnPool::runNo("DROP TABLE IF EXISTS `user`;");
        QLsDBConnPool::runNo(
            "CREATE TABLE `user` ("
            "  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,"
            "  `username` varchar(16) DEFAULT NULL,"
            "  `password` varchar(16) DEFAULT NULL,"
            "  `mobile` varchar(11) DEFAULT NULL,"
            "  `email` varchar(48) DEFAULT NULL,"
            "  `date` varchar(19) DEFAULT NULL"
            ");");
    }

    /* 清理缓存 */
    userCache.clear();
    usersCache.clear();
}
